package utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import business.CellXLEW;

public class POIUtils {

	public static Workbook getWorkbook(File content) {
		InputStream is = null;
		Workbook wb = null;
		try {
			is = new FileInputStream(content);
			wb = WorkbookFactory.create(is);
		} catch (Exception e) {
			return null;
		} catch (Throwable e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return wb;
	}
	
	public static void setCellValue(Workbook wb,CellXLEW cellXLEW,String value)
	{
		Sheet sheet = wb.getSheetAt(cellXLEW.getSheet() - 1);
		if (sheet != null) {
			Row row = sheet.getRow(cellXLEW.getRow() - 1);
			if (row != null) {
				Cell cellWb = row.getCell(cellXLEW.getCol() - 1);
				if (cellWb != null) {
//					CellStyle cellStyle = wb.createCellStyle();
//					cellStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());
//					cellWb.setCellStyle(cellStyle);
					if (cellWb.getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING) {
						if (value.startsWith("=")) {
							cellWb.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA);
							try {
								cellWb.setCellFormula(cellWb.getCellFormula()
										+ value.substring(1, value.length()));
							} catch (Exception e) {
								cellWb.setCellFormula(value.substring(1, value.length()));
								e.printStackTrace();
							}
						} else {
							cellWb.setCellValue(cellWb.getStringCellValue() + value);
						}
					} else if (cellWb.getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA) {
						if (value.startsWith("=")) {
							try {
								cellWb.setCellFormula(cellWb.getCellFormula()
										+ value.substring(1, value.length()));
							} catch (Exception e) {
								cellWb.setCellFormula(value.substring(1, value.length()));
								e.printStackTrace();
							}
						} else {
							try {
								cellWb.setCellFormula(cellWb.getCellFormula());
							} catch (Exception e) {
								cellWb.setCellFormula(value);
								e.printStackTrace();
							}
						}
					} else {
						try {
							cellWb.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
							cellWb.setCellValue(value);
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
	}
	
	public static String getCellValue(Workbook wb,CellXLEW cellXLEW) {
		String value = "";
		Sheet sheet = wb.getSheetAt(cellXLEW.getSheet() - 1);
		if (sheet != null) {
			Row row = sheet.getRow(cellXLEW.getRow() - 1);
			if (row != null) {
				Cell cell = row.getCell(cellXLEW.getCol() - 1);
				if (cell != null) {
					switch (cell.getCellType()) {
					case Cell.CELL_TYPE_FORMULA:
						FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
						CellValue cellValueWb = evaluator.evaluate(cell);
						
						switch (cellValueWb.getCellType()) {
						case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
//							value = String.valueOf(cellValueWb
//									.getNumberValue());
							value = (new BigDecimal(Double.toString(cellValueWb
									.getNumberValue()))).toPlainString();
							break;
						case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
							value = cellValueWb.getStringValue();
							break;
						}
						break;
					case Cell.CELL_TYPE_NUMERIC:
//						value = String.valueOf(cell.getNumericCellValue());
						value = (new BigDecimal(Double.toString(cell.getNumericCellValue()))).toPlainString();
						break;
					case Cell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					default:
						value = cell.getStringCellValue();
					}
				}
			}
		}
		return value.replace(".", ",");
	}
	
	public static void validateCell(CellXLEW cellXLEW, String[] combo, Workbook workbook, int nro) {
		
		Cell cellWb = getCell(cellXLEW,workbook);
		Sheet sheetWb = getSheet(cellXLEW, workbook);
		
		if(cellWb != null && sheetWb != null)
		{
			CellRangeAddressList current = new CellRangeAddressList(
					cellWb.getRowIndex(), cellWb.getRowIndex(),
					cellWb.getColumnIndex(), cellWb.getColumnIndex());
			DataValidation dataValidation;

			String name = "hiddenList" + nro;
			org.apache.poi.ss.usermodel.Sheet hideSheet = workbook
					.createSheet(name);
			nro++;

			int i = 0;
			while (i < combo.length) {
				org.apache.poi.ss.usermodel.Cell hideCell = hideSheet.createRow(i)
						.createCell(0);
				hideCell.setCellValue(combo[i]);

				i++;
			}
			if (hideSheet instanceof HSSFSheet) {
				dataValidation = new HSSFDataValidation(current,
						DVConstraint.createFormulaListConstraint("'" + name
								+ "'!$A$1:$A$" + String.valueOf(combo.length)));
			} else {
				XSSFDataValidationHelper helper = new XSSFDataValidationHelper(
						(XSSFSheet) hideSheet);
				DataValidationConstraint constraint = helper
						.createFormulaListConstraint("'" + name + "'!$A$1:$A$"
								+ String.valueOf(combo.length));

				dataValidation = helper.createValidation(constraint, current);
			}
			sheetWb.addValidationData(dataValidation);

			int index = workbook.getSheetIndex(name);
			workbook.setSheetHidden(index, 2);
		}		
	}
	
	private static Cell getCell(CellXLEW cellXLEW,Workbook workbook)
	{
		Sheet sheetWb = getSheet(cellXLEW,workbook);
		if (sheetWb != null) {
			Row row = sheetWb.getRow(cellXLEW.getRow() - 1);
			if (row != null) {
				Cell cellWb = row.getCell(cellXLEW.getCol() - 1);
				if (cellWb != null) {
					return cellWb;
				}
			}
		}
		return null;
	}
	
	private static Sheet getSheet(CellXLEW cellXLEW,Workbook workbook)
	{
		Sheet sheetWb = workbook.getSheetAt(cellXLEW.getSheet() - 1);
		return sheetWb;
	}
	
}
